﻿using Sql.DataAttributes;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;

namespace Sql
{
    public sealed class Data
    {
        private static volatile Data instance;
        private static object syncRoot = new object();
        // The available connections to data stores
        private static Dictionary<string, string> connections;
        // If the application is partial trust, we can use any code that requires
        // ReflectionPermission.
        public bool IsPartialTrust { get; set; }

        private Data()
        {
            connections = new Dictionary<string, string>();
        }

        /// <summary>
        /// The public facing instance of the singleton
        /// </summary>
        public static Data Store
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        if (instance == null)
                        {
                            instance = new Data();
                        }
                    }
                }

                return instance;
            }
        }

        #region Generate Query Text

        private bool generateQueryText = false;
        public bool GenerateQueryText
        {
            get { return generateQueryText; }
            set
            {
                generateQueryText = value;
                if (!generateQueryText)
                    currentSqlQuery = string.Empty;
            }
        }

        #endregion Generate Query Text

        #region Current SQL Query

        private string currentSqlQuery = string.Empty;

        public string CurrentSqlQuery
        {
            get { return currentSqlQuery; }
        }

        #endregion CurrentSQL Query

        #region Connections

        private KeyValuePair<string, string> DefaultConnection
        {
            get { return connections.First(); }
        }

        public void AddConnection(string name, string connectionString)
        {
            connections.Add(name, connectionString);
        }

        public bool ContainsConnection(string name)
        {
            return connections.ContainsKey(name);
        }

        internal string GetConnectionForType(Type type)
        {
            // get the correct connection information
            var assignedConnection = Connection.GetConnection(type);
            return assignedConnection == null ? DefaultConnection.Value : connections[assignedConnection.Value];
        }

        internal string GetConnectionNameForType(Type type)
        {
            // get the correct connection information
            var assignedConnection = Connection.GetConnection(type);
            return assignedConnection == null ? DefaultConnection.Key : assignedConnection.Value;
        }

        #endregion Connections

        #region Query

        /// <summary>
        /// Executes a query without returning any results.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="connection"></param>
        /// <param name="parameters"></param>
        internal int QueryInternal(string query, string connection, params Parameter[] parameters)
        {
            // get the correct connection information
            var connectionStr = connection == null ? DefaultConnection.Value : connections[connection];

            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                using (var command = new SqlCommand(query, conn))
                {
                    conn.Open();

                    BuildParameterList(command, parameters);

                    if (GenerateQueryText)
                        GenerateSqlQuery(command);

                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Executes a query without returning any results.
        /// </summary>
        /// <param name="query"></param>
        public int Query(string query)
        {
            return QueryInternal(query, null, null);
        }

        /// <summary>
        /// Executes a query without returning any results.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="connection">The named connection</param>
        public int Query(string query, string connection)
        {
            return QueryInternal(query, connection, null);
        }

        /// <summary>
        /// Executes a query without returning any results.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        public int Query(string query, params Parameter[] parameters)
        {
            return QueryInternal(query, null, parameters);
        }

        /// <summary>
        /// Executes a query without returning any results.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="connection"></param>
        /// <param name="parameters"></param>
        public int Query(string query, string connection, params Parameter[] parameters)
        {
            return QueryInternal(query, connection, parameters);
        }

        #endregion Query

        #region Query<T>

        /// <summary>
        /// Executes a query and returns an IEnumerable of results.
        /// </summary>
        /// <typeparam name="T">The entity or type of results to return</typeparam>
        /// <param name="connection">The named connction</param>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        internal IEnumerable<T> QueryInternal<T>(string query, string connection, params Parameter[] parameters)
        {
            var type = typeof(T);

            if (connection == null)
            {
                connection = GetConnectionNameForType(type);
            }

            string connectionStr = connection;
            if (connections.ContainsKey(connection))
            {
                // get the correct connection information
                connectionStr = connections[connection];
            }

            if (type.IsValueType || type == typeof(string))
            {
                // The type is a simple value type or string. We do not need the complext activator.
                using (SqlConnection conn = new SqlConnection(connectionStr))
                {
                    using (var command = new SqlCommand(query, conn))
                    {
                        conn.Open();

                        BuildParameterList(command, parameters);

                        if (GenerateQueryText)
                            GenerateSqlQuery(command);

                        using (var reader = command.ExecuteReader())
                        {
                            var map = GetPropertyMap<T>(reader);

                            while (reader.Read())
                            {
                                var instance = Activator.CreateInstance<T>();
                                var item = reader[0];
                                instance = Convert.IsDBNull(item) ? default(T) : (T)item;
                                yield return instance;
                            }
                        }
                    }
                }
            }
            else
            {
                // get activator
                var activator = ObjectGenerator<T>();

                using (SqlConnection conn = new SqlConnection(connectionStr))
                {
                    using (var command = new SqlCommand(query, conn))
                    {
                        conn.Open();

                        BuildParameterList(command, parameters);

                        if (GenerateQueryText)
                            GenerateSqlQuery(command);

                        using (var reader = command.ExecuteReader())
                        {
                            var map = GetPropertyMap<T>(reader);

                            while (reader.Read())
                            {
                                var instance = activator();

                                foreach (var property in map)
                                {
                                    // do not populate ignore properties
                                    if (property.Key.IsIgnore())
                                        continue;

                                    if (!HasColumn(reader, property.Key.Name))
                                        continue;

                                    var item = reader[property.Value];
                                    var realType = Nullable.GetUnderlyingType(property.Key.PropertyType) ?? property.Key.PropertyType;
                                    var value = Convert.IsDBNull(item) ? null : Convert.ChangeType(item, realType);
                                    DataCache.Cache.GetSetter<T>(property.Key)(instance, value);
                                }

                                yield return instance;
                            }
                        }
                    }
                }
            }
        }

        internal IEnumerable<T> QueryInternalWithPartialTrust<T>(string query, string connection, params Parameter[] parameters)
        {
            var type = typeof(T);

            if (connection == null)
            {
                connection = GetConnectionNameForType(type);
            }

            string connectionStr = connection;
            if (connections.ContainsKey(connection))
            {
                // get the correct connection information
                connectionStr = connections[connection];
            }

            if (type.IsValueType || type == typeof(string))
            {
                // The type is a simple value type or string. We do not need the complext activator.
                using (SqlConnection conn = new SqlConnection(connectionStr))
                {
                    using (var command = new SqlCommand(query, conn))
                    {
                        conn.Open();

                        BuildParameterList(command, parameters);

                        if (GenerateQueryText)
                            GenerateSqlQuery(command);

                        using (var reader = command.ExecuteReader())
                        {
                            var map = GetPropertyMap<T>(reader);

                            while (reader.Read())
                            {
                                var instance = Activator.CreateInstance<T>();
                                var item = reader[0];
                                instance = Convert.IsDBNull(item) ? default(T) : (T)item;
                                yield return instance;
                            }
                        }
                    }
                }
            }
            else
            {
                using (SqlConnection conn = new SqlConnection(connectionStr))
                {
                    using (var command = new SqlCommand(query, conn))
                    {
                        conn.Open();

                        BuildParameterList(command, parameters);

                        if (GenerateQueryText)
                            GenerateSqlQuery(command);

                        using (var reader = command.ExecuteReader())
                        {
                            var map = GetPropertyMap<T>(reader);

                            while (reader.Read())
                            {
                                var instance = Activator.CreateInstance<T>();

                                foreach (var property in map)
                                {
                                    // do not populate ignore properties
                                    if (property.Key.IsIgnore())
                                        continue;

                                    var item = reader[property.Value];
                                    var value = Convert.IsDBNull(item) ? null : item;
                                    property.Key.SetValue(instance, value, null);
                                }

                                yield return instance;
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Executes a query and returns an IEnumerable of results.
        /// </summary>
        /// <typeparam name="T">The entity or type of results to return</typeparam>
        public IEnumerable<T> Query<T>(string query)
        {
            if (IsPartialTrust)
                return QueryInternalWithPartialTrust<T>(query, null, null);
            else
                return QueryInternal<T>(query, null, null);
        }

        /// <summary>
        /// Executes a query and returns an IEnumerable of results.
        /// </summary>
        /// <typeparam name="T">The entity or type of results to return</typeparam>
        /// <param name="connection">The named connction</param>
        public IEnumerable<T> Query<T>(string query, string connection)
        {
            if (IsPartialTrust)
                return QueryInternalWithPartialTrust<T>(query, connection, null);
            else
                return QueryInternal<T>(query, connection, null);
        }

        /// <summary>
        /// Executes a query and returns an IEnumerable of results.
        /// </summary>
        /// <typeparam name="T">The entity or type of results to return</typeparam>
        /// <param name="parameters"></param>
        public IEnumerable<T> Query<T>(string query, params Parameter[] parameters)
        {
            if (IsPartialTrust)
                return QueryInternalWithPartialTrust<T>(query, null, parameters);
            else
                return QueryInternal<T>(query, null, parameters);
        }

        /// <summary>
        /// Executes a query and returns an IEnumerable of results.
        /// </summary>
        /// <typeparam name="T">The entity or type of results to return</typeparam>
        /// <param name="connection">The named connction</param>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(string query, string connection, params Parameter[] parameters)
        {
            if (IsPartialTrust)
                return QueryInternalWithPartialTrust<T>(query, connection, parameters);
            else
                return QueryInternal<T>(query, connection, parameters);
        }

        #endregion Query<T>

        #region QueryMultiple<T>

        internal dynamic QueryMultipleInternal(string query, string connection, string[] tables, params Parameter[] parameters)
        {
            if (connection == null)
            {
                connection = DefaultConnection.Key;
            }

            string connectionStr = connection;
            if (connections.ContainsKey(connection))
            {
                // get the correct connection information
                connectionStr = connections[connection];
            }

            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                using (var command = new SqlCommand(query, conn))
                {
                    conn.Open();

                    BuildParameterList(command, parameters);

                    if (GenerateQueryText)
                        GenerateSqlQuery(command);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        var result = (IDictionary<string, object>)new ExpandoObject();
                        var schemas = new List<dynamic>();

                        int count = 1;
                        do
                        {
                            var results = new List<dynamic>();
                            string tableName = "Table" + count;

                            if (tables != null && tables.Length >= count)
                                tableName = tables[count - 1];

                            while (reader.Read())
                            {
                                dynamic item = new ExpandoObject();

                                for (var a = 0; a < reader.FieldCount; a++)
                                {
                                    var name = reader.GetName(a);
                                    if (name == "DataManager.TableName")
                                        continue;
                                    var value = reader[a];

                                    ((IDictionary<string, object>)item)[name] = Convert.IsDBNull(value) ? null : value;
                                }

                                results.Add(item);
                            }

                            result[tableName] = results;
                            count++;
                        } while (reader.NextResult());

                        return result;
                    }
                }
            }
        }

        public dynamic QueryMultiple(string query)
        {
            return QueryMultipleInternal(query, null, null, null);
        }

        public dynamic QueryMultiple(string query, string connection)
        {
            return QueryMultipleInternal(query, connection, null, null);
        }

        public dynamic QueryMultiple(string query, params Parameter[] parameters)
        {
            return QueryMultipleInternal(query, null, null, parameters);
        }

        public dynamic QueryMultiple(string query, string connection, params Parameter[] parameters)
        {
            return QueryMultipleInternal(query, connection, null, parameters);
        }

        public dynamic QueryMultiple(string query, string[] tables)
        {
            return QueryMultipleInternal(query, null, tables, null);
        }

        public dynamic QueryMultiple(string query, string connection, string[] tables)
        {
            return QueryMultipleInternal(query, connection, tables, null);
        }

        public dynamic QueryMultiple(string query, string[] tables, params Parameter[] parameters)
        {
            return QueryMultipleInternal(query, null, tables, parameters);
        }

        public dynamic QueryMultiple(string query, string connection, string[] tables, params Parameter[] parameters)
        {
            return QueryMultipleInternal(query, connection, tables, parameters);
        }


        #endregion QueryMultiple<T>

        #region QueryDynamic

        internal IEnumerable<dynamic> QueryDynamicInternal(string query, string connection, params Parameter[] parameters)
        {
            if (connection == null)
            {
                connection = DefaultConnection.Key;
            }

            string connectionStr = connection;
            if (connections.ContainsKey(connection))
            {
                // get the correct connection information
                connectionStr = connections[connection];
            }

            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                using (var command = new SqlCommand(query, conn))
                {
                    conn.Open();

                    BuildParameterList(command, parameters);

                    if (GenerateQueryText)
                        GenerateSqlQuery(command);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dynamic item = new ExpandoObject();

                            for (var a = 0; a < reader.FieldCount; a++)
                            {
                                var name = reader.GetName(a);
                                var value = reader[a];

                                ((IDictionary<string, object>)item)[name] = Convert.IsDBNull(value) ? null : value;
                            }

                            yield return item;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Returns dynamic results of a SQL query
        /// </summary>
        /// <param name="query">The query to be run against the database</param>
        /// <returns>A collection of dynamic objects</returns>
        public IEnumerable<dynamic> QueryDynamic(string query)
        {
            return QueryDynamicInternal(query, null, null);
        }

        /// <summary>
        /// Returns dynamic results of a SQL query
        /// </summary>
        /// <param name="query">The query to be run against the database</param>
        /// <param name="connection">The connection to run the query through</param>
        /// <returns>A collection of dynamic objects</returns>
        public IEnumerable<dynamic> QueryDynamic(string query, string connection)
        {
            return QueryDynamicInternal(query, connection, null);
        }

        /// <summary>
        /// Returns dynamic results of a SQL query
        /// </summary>
        /// <param name="query">The query to be run against the database</param>
        /// <param name="parameters">Query parameters</param>
        /// <returns>A collection of dynamic objects</returns>
        public IEnumerable<dynamic> QueryDynamic(string query, params Parameter[] parameters)
        {
            return QueryDynamicInternal(query, null, parameters);
        }

        /// <summary>
        /// Returns dynamic results of a SQL query
        /// </summary>
        /// <param name="query">The query to be run against the database</param>
        /// <param name="connection">The connection to run the query through</param>
        /// <param name="parameters">Query parameters</param>
        /// <returns>A collection of dynamic objects</returns>
        public IEnumerable<dynamic> QueryDynamic(string query, string connection, params Parameter[] parameters)
        {
            return QueryDynamicInternal(query, connection, parameters);
        }

        #endregion QueryDynamic

        #region Scalar

        internal T ScalarInternal<T>(string query, string connection, params Parameter[] parameters)
        {
            var type = typeof(T);
            T result = default(T);

            if (connection == null)
            {
                connection = GetConnectionNameForType(type);
            }

            string connectionStr = connection;
            if (connections.ContainsKey(connection))
            {
                // get the correct connection information
                connectionStr = connections[connection];
            }

            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                var command = new SqlCommand(query, conn);
                conn.Open();

                BuildParameterList(command, parameters);

                if (GenerateQueryText)
                    GenerateSqlQuery(command);

                var reader = command.ExecuteReader();

                // only fetch first row
                if (reader.Read())
                {
                    if (type.IsValueType || type == typeof(string))
                    {
                        var item = reader[0];
                        result = Convert.IsDBNull(item) ? default(T) : (T)item;
                    }
                    else
                    {
                        result = Activator.CreateInstance<T>();
                        // read the data from the row into each property in the type
                        foreach (var pInfo in type.GetProperties())
                        {
                            // do not populate ignored properties
                            if (pInfo.IsIgnore())
                                continue;

                            if (!HasColumn(reader, pInfo.Name))
                                continue;

                            // get the value that matches the property name
                            var item = reader[pInfo.Name];
                            var realType = Nullable.GetUnderlyingType(pInfo.PropertyType) ?? pInfo.PropertyType;
                            var value = Convert.IsDBNull(item) ? null : Convert.ChangeType(item, realType);
                            DataCache.Cache.GetSetter<T>(pInfo)(result, value);
                        }
                    }
                }
            }

            return result;
        }

        internal T ScalarInternalWithPartialTrust<T>(string query, string connection, params Parameter[] parameters)
        {
            var type = typeof(T);
            T result = default(T);

            if (connection == null)
            {
                connection = GetConnectionNameForType(type);
            }

            string connectionStr = connection;
            if (connections.ContainsKey(connection))
            {
                // get the correct connection information
                connectionStr = connections[connection];
            }

            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                var command = new SqlCommand(query, conn);
                conn.Open();

                BuildParameterList(command, parameters);

                if (GenerateQueryText)
                    GenerateSqlQuery(command);

                var reader = command.ExecuteReader();

                // only fetch first row
                if (reader.Read())
                {
                    if (type.IsValueType || type == typeof(string))
                    {
                        var item = reader[0];
                        result = Convert.IsDBNull(item) ? default(T) : (T)item;
                    }
                    else
                    {
                        result = Activator.CreateInstance<T>();
                        // read the data from the row into each property in the type
                        foreach (var pInfo in type.GetProperties())
                        {
                            // do not populate ignored properties
                            if (pInfo.IsIgnore())
                                continue;

                            if (!HasColumn(reader, pInfo.Name))
                                continue;

                            // get the value that matches the property name
                            var item = reader[pInfo.Name];
                            var value = Convert.IsDBNull(item) ? null : item;
                            pInfo.SetValue(result, value, null);
                        }
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// Returns the scalar result of a SQL query
        /// </summary>
        /// <typeparam name="T">The type of the scalar result</typeparam>
        /// <param name="query">The query to be run against the database</param>
        /// <returns>A scalar result</returns>
        public T Scalar<T>(string query)
        {
            if (IsPartialTrust)
                return ScalarInternalWithPartialTrust<T>(query, null, null);
            else
                return ScalarInternal<T>(query, null, null);
        }

        /// <summary>
        /// Returns the scalar result of a SQL query
        /// </summary>
        /// <typeparam name="T">The type of the scalar result</typeparam>
        /// <param name="query">The query to be run against the database</param>
        /// <param name="connection">The connection to run the query through</param>
        /// <returns>A scalar result</returns>
        public T Scalar<T>(string query, string connection)
        {
            if (IsPartialTrust)
                return ScalarInternalWithPartialTrust<T>(query, connection, null);
            else
                return ScalarInternal<T>(query, connection, null);
        }

        /// <summary>
        /// Returns the scalar result of a SQL query
        /// </summary>
        /// <typeparam name="T">The type of the scalar result</typeparam>
        /// <param name="query">The query to be run against the database</param>
        /// <param name="parameters">Query parameters</param>
        /// <returns>A scalar result</returns>
        public T Scalar<T>(string query, params Parameter[] parameters)
        {
            if (IsPartialTrust)
                return ScalarInternalWithPartialTrust<T>(query, null, parameters);
            else
                return ScalarInternal<T>(query, null, parameters);
        }

        /// <summary>
        /// Returns the scalar result of a SQL query
        /// </summary>
        /// <typeparam name="T">The type of the scalar result</typeparam>
        /// <param name="query">The query to be run against the database</param>
        /// <param name="connection">The connection to run the query through</param>
        /// <param name="parameters">Query parameters</param>
        /// <returns>A scalar result</returns>
        public T Scalar<T>(string query, string connection, params Parameter[] parameters)
        {
            if (IsPartialTrust)
                return ScalarInternalWithPartialTrust<T>(query, connection, parameters);
            else
                return ScalarInternal<T>(query, connection, parameters);
        }

        #endregion Scalar

        public IEnumerable<T> All<T>()
        {
            var type = typeof(T);

            var tableName = string.Empty;

            var connection = Data.Store.GetConnectionNameForType(type);

            #region Get Table Name
            var tName = (Name)type.GetCustomAttributes(false).SingleOrDefault(x => x is Name);
            tableName = tName == null ? type.Name : tName.name;
            #endregion Get Table Name

            var command = "SELECT {Columns} FROM [{Table}]";
            var columns = new StringBuilder();

            var first = true;
            foreach (var pInfo in type.GetProperties())
            {
                if (pInfo.IsIgnore())
                {
                    continue;
                }

                if (!first)
                {
                    columns.Append(",");
                }

                var name = pInfo.Name;
                columns.Append(string.Format("[{0}]", name));
                first = false;
            }

            command = command.ReplaceDictionary(new Dictionary<string, string>()
            {
                { "{Table}", tableName },
                { "{Columns}", columns.ToString() }
            });

            return Data.Store.Query<T>(command, connection);
        }

        private Dictionary<PropertyInfo, int> GetPropertyMap<T>(System.Data.IDataReader reader)
        {
            var type = typeof(T);

            // get the properties of T and build a map
            var map = new Dictionary<PropertyInfo, int>();
            foreach (var pInfo in type.GetProperties())
            {
                if (pInfo.IsIgnore())
                    continue;
                map.Add(pInfo, reader.GetOrdinal(pInfo.Name));
            }

            return map;
        }

        private static Func<T> ObjectGenerator<T>()
        {
            var type = typeof(T);
            var target = type.GetConstructor(Type.EmptyTypes);
            var dynamic = new DynamicMethod(string.Empty, type, new Type[0], target.DeclaringType);
            var il = dynamic.GetILGenerator();
            il.DeclareLocal(target.DeclaringType);
            il.Emit(OpCodes.Newobj, target);
            il.Emit(OpCodes.Stloc_0);
            il.Emit(OpCodes.Ldloc_0);
            il.Emit(OpCodes.Ret);

            return (Func<T>)dynamic.CreateDelegate(typeof(Func<T>));
        }

        private void BuildParameterList(SqlCommand command, Parameter[] parameters)
        {
            // add parameters if present
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    var p = new SqlParameter(parameter.Name, parameter.Value);
                    if (parameter.Value == null)
                        p.Value = DBNull.Value;
                    command.Parameters.Add(p);
                }
            }
        }

        private void GenerateSqlQuery(SqlCommand command)
        {
            currentSqlQuery = command.CommandText;

            foreach (SqlParameter parameter in command.Parameters)
            {
                var pType = parameter.SqlDbType;

                if (parameter.Value == DBNull.Value)
                {
                    currentSqlQuery = currentSqlQuery.Replace("@" + parameter.ParameterName, "NULL");
                }
                else if (pType == SqlDbType.DateTime || pType == SqlDbType.DateTime2 || pType == SqlDbType.NChar
                    || pType == SqlDbType.NText || pType == SqlDbType.NVarChar || pType == SqlDbType.SmallDateTime
                    || pType == SqlDbType.Text || pType == SqlDbType.VarChar)
                {
                    currentSqlQuery = currentSqlQuery.Replace("@" + parameter.ParameterName, string.Format("'{0}'", parameter.Value));
                }
                else
                {
                    currentSqlQuery = currentSqlQuery.Replace("@" + parameter.ParameterName, parameter.Value.ToString());
                }
            }
        }

        public static bool HasColumn(DbDataReader Reader, string ColumnName)
        {
            foreach (DataRow row in Reader.GetSchemaTable().Rows)
            {
                if (row["ColumnName"].ToString() == ColumnName)
                    return true;
            } //Still here? Column not found. 
            return false;
        }
    }

    public static class StoreEntityExtensions
    {
        public static T Create<T>(this T entity)
        {
            // perform validation
            ((IEntity)entity).Validate(DataOperation.Insert);

            var entityType = entity.GetType();
            var tableName = string.Empty;

            var connection = Data.Store.GetConnectionNameForType(entityType);

            #region Get Table Name
            var tName = (Name)entityType.GetCustomAttributes(false).SingleOrDefault(x => x is Name);
            tableName = tName == null ? entityType.Name : tName.name;
            #endregion Get Table Name

            var command = "INSERT INTO [{Table}] ({Columns}) VALUES ({Values}); SELECT TOP 1 {PrimaryKey},{Columns} FROM [{Table}] WHERE [{PrimaryKey}] = SCOPE_IDENTITY();";
            var primaryKey = string.Empty;
            var columns = new StringBuilder();
            var values = new StringBuilder();
            var parameters = new List<Parameter>();

            var first = true;
            foreach (var pInfo in entityType.GetProperties())
            {
                if (pInfo.IsKey())
                {
                    primaryKey = pInfo.Name;
                    continue;
                }
                if (pInfo.IsIgnore() || pInfo.IsReadOnly())
                {
                    continue;
                }

                if (!first)
                {
                    columns.Append(",");
                    values.Append(",");
                }

                var name = pInfo.Name;
                var value = pInfo.GetValue(entity, null);
                columns.Append(string.Format("[{0}]", name));
                values.Append(string.Format("@{0}", name));
                parameters.Add(Parameter.Create(name, value ?? DBNull.Value));
                first = false;
            }

            command = command.ReplaceDictionary(new Dictionary<string, string>()
            {
                { "{Table}", tableName },
                { "{Columns}", columns.ToString() },
                { "{Values}", values.ToString() },
                { "{PrimaryKey}", primaryKey }
            });

            return Data.Store.Scalar<T>(command, connection, parameters.ToArray());
        }

        public static T Update<T>(this T entity)
        {
            // perform validation
            ((IEntity)entity).Validate(DataOperation.Update);

            var entityType = entity.GetType();
            var tableName = string.Empty;

            var connection = Data.Store.GetConnectionNameForType(entityType);

            #region Get Table Name
            var tName = (Name)entityType.GetCustomAttributes(false).SingleOrDefault(x => x is Name);
            tableName = tName == null ? entityType.Name : tName.name;
            #endregion Get Table Name

            var command = "UPDATE [{Table}] SET {Columns} WHERE [{PrimaryKey}] = @Id; SELECT TOP 1 {PrimaryKey},{Columns} FROM [{Table}] WHERE [{PrimaryKey}] = @Id;";
            var primaryKey = string.Empty;
            var columns = new StringBuilder();
            var parameters = new List<Parameter>();

            var first = true;
            foreach (var pInfo in entityType.GetProperties())
            {
                // do not insert key
                if (pInfo.IsKey())
                {
                    primaryKey = pInfo.Name;
                    parameters.Add(Parameter.Create("Id", pInfo.GetValue(entity, null)));
                    continue;
                }
                if (pInfo.IsIgnore() || pInfo.IsReadOnly())
                {
                    continue;
                }

                if (!first)
                {
                    columns.Append(",");
                }

                var name = pInfo.Name;
                var value = pInfo.GetValue(entity, null);
                columns.Append(string.Format("[{0}] = @{0}", name));
                parameters.Add(Parameter.Create(name, value ?? DBNull.Value));
                first = false;
            }

            command = command.ReplaceDictionary(new Dictionary<string, string>()
            {
                { "{Table}", tableName },
                { "{Columns}", columns.ToString() },
                { "{PrimaryKey}", primaryKey }
            });

            return Data.Store.Scalar<T>(command, connection, parameters.ToArray());
        }

        public static void Delete<T>(this T entity)
        {
            // perform validation
            ((IEntity)entity).Validate(DataOperation.Delete);

            var entityType = entity.GetType();
            var tableName = string.Empty;

            var connection = Data.Store.GetConnectionNameForType(entityType);

            #region Get Table Name
            var tName = (Name)entityType.GetCustomAttributes(false).SingleOrDefault(x => x is Name);
            tableName = tName == null ? entityType.Name : tName.name;
            #endregion Get Table Name

            var command = "DELETE FROM [{Table}] WHERE [{PrimaryKey}] = @Id;";
            var primaryKey = string.Empty;
            var parameters = new List<Parameter>();

            foreach (var pInfo in entityType.GetProperties())
            {
                if (pInfo.IsKey())
                {
                    // we found the key which is all we need, now break 
                    primaryKey = pInfo.Name;
                    parameters.Add(Parameter.Create("Id", pInfo.GetValue(entity, null)));
                    break;
                }
            }

            command = command.ReplaceDictionary(new Dictionary<string, string>()
            {
                { "{Table}", tableName },
                { "{PrimaryKey}", primaryKey }
            });

            Data.Store.QueryInternal(command, connection, parameters.ToArray());
        }

        public static void CreateAll<T>(this IList<T> data)
        {
            Type type = typeof(T);
            var connectionStr = Data.Store.GetConnectionForType(type);

            #region Get Table Name
            var tName = (Name)type.GetCustomAttributes(false).SingleOrDefault(x => x is Name);
            var tableName = tName == null ? type.Name : tName.name;
            #endregion Get Table Name

            var dt = data.ToDataTable();

            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                conn.Open();

                using (SqlBulkCopy copy = new SqlBulkCopy(conn))
                {
                    copy.DestinationTableName = tableName;
                    copy.WriteToServer(dt);
                }
            }
        }

        private static DataTable ToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }
    }
}
